Modern computer database environments often include a client computer system connected to a server computer system containing one or more data repositories (such as relational database management systems). The server computer system also has stored therein one or more stored procedures. Each stored procedure, when executed, interacts with and performs database operations (such as a database query) on one or more of the data repositories.
A client application in the client computer system communicates with the server computer system and causes one of the stored procedures to be executed. Diverse query result sets are generated as a result of such execution. These diverse query result sets are transferred from the server computer system to the client application in the client computer system.
Some database products on the market today offer facilities to return query result sets by a stored procedure, as described above. However, these database products are typically constrained in some manner.
For example, some conventional database products are limited in the number of query result sets that may be returned by any given stored procedure invocation. In particular, in some conventional database products, the stored procedures are limited to a single query result set on any given stored procedure invocation.
Other conventional database products support stored procedures that can return multiple query result sets. However, these conventional database products are limited in various ways. In particular, some of these conventional database products force the client application to fetch and process each result set in the order in which it is transmitted by the server computer system. This places artificial limitations on the client application designer, and makes it difficult to take full advantage of the data returned by the stored procedure.
Also, with some of these conventional database products, if the client application chooses to fetch a subset of the rows in a large query, the client process (which is contained in the client computer system and which operates as an interface between the client application and the server computer system) must receive and discard all the remaining rows in the query result set, before the data rows for the next query can be presented to the client application. This results in increased CPU and I/O cost to both the client computer system and the server computer system.
Further, some of these conventional database products require each result set to have the same number of columns and the same column attributes. Essentially, the result set is a union of all the result set columns. This can cause the server computer system to transmit many extra data columns that are only present to force consistent attributes for the result set.